import numpy as np
import pandas as pd

# 将数据按区县分开存表
basedir = 'data/年鉴统计数据/县域数据/最终成果/滇中县域-统一时序.xlsx'
df_empty = pd.DataFrame()
# [2005, 2006, 2007, 2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019]
# [2005, 2010, 2015, 2019]
years = [2005, 2010, 2015, 2019]
data = {}
df_xzq = pd.read_excel('data/年鉴统计数据/县域数据/县域指标分类体系.xlsx', '行政区')
xzqs = np.array(df_xzq['区县简称'])
df_zb = pd.read_excel('data/年鉴统计数据/县域数据/县域指标分类体系.xlsx', '新型城镇化-spilt') #新型城镇化-spilt ，生态环境-spilt
print('--------------------指标-------------------')
print(df_zb.columns)

for xzq in xzqs:
    zb = np.array(df_zb.columns)
    data[xzq] = pd.DataFrame(index=years, columns=zb)
# print('--------------------数据字典-------------------')
# print(data)

for year in years:
    df = pd.read_excel(basedir, str(year))
    print('滇中县域' + str(year) + '=================原始数据====================')
    print(df)
    # 提取指定列数据
    df_sub = pd.DataFrame(index=df.index)
    for column in df_zb.columns:
        df_sub[column] = df[column]
    print('滇中县域' + str(year) + '=================提取数据====================')
    print(df_sub)
    for i in df_sub.index:
        name = df_sub.loc[i].values[0]
        values = df_sub.loc[i].values
        df2 = data[name]
        df2.loc[year] = values

# print('----------------------------data----------------------------------')
# print(data)
with pd.ExcelWriter('滇中县域-独立时序-新型城镇化-4个年度.xlsx') as writer:
    for xzq in data:
        dfd = data[xzq]
        dfd.drop(columns='行政区', axis=1, inplace=True)
        dfd.to_excel(writer, sheet_name=xzq)
    # writer.save()
    # writer.close()
